<?php

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Cell\DataType;

class Lib_XlsxRead
{
    private static $obj;

    private function __construct()
    {
    }

    /**
     * Undocumented function
     *
     * @param string $savePath
     * @return Lib_XlsxExport
     */
    public static function getInstance()
    {
        if (is_null(self::$obj)) {
            self::$obj = new self();
        }

        return self::$obj;
    }

    public function __clone()
    {
    }

    /**
     * 导出  excel
     *
     * @param array $exportData 需要导出的数据
     * @return void
     */
    /**
     * 解析excel.
     * @param string $realPath
     * @param int $parseRow
     * @return array
     * @throws \PhpOffice\PhpSpreadsheet\Exception|Exception
     */
    public function execute(string $realPath, int $parseRow = 2): array
    {
        $res = [];
        $ext = strtolower(pathinfo($realPath, PATHINFO_EXTENSION));
        if (!in_array($ext, ['xlsx', 'xls'])) {
            throw new \Exception('非法文件格式,标准为.xls或.xlsx结尾，传入文件名《' . pathinfo($realPath, PATHINFO_BASENAME) . '》', 1);
        }
        $reader = null;
        try {
            // 加载 Excel 文件
            $spreadsheet = IOFactory::load($realPath);
            // 获取活动的工作表
            $worksheet = $spreadsheet->getActiveSheet();

            // 获取最高行号和列号
            $highestRow = $worksheet->getHighestRow();
            $highestColumn = $worksheet->getHighestColumn();
            $highestColumnIndex = Coordinate::columnIndexFromString($highestColumn);
            // 遍历行
            for ($row = $parseRow; $row <= $highestRow; $row++) {
                $b = 0;
                $isEmptyRow = false; // 假设当前行为空白行
                $tmp = [];
                // 遍历列
                for ($col = 1; $col <= $highestColumnIndex; $col++) {
                    $b++;
                    $cell = $worksheet->getCellByColumnAndRow($col, $row);
                    $dataType = $cell->getDataType();
                    // 根据数据类型读取值
                    switch ($dataType) {
                        case DataType::TYPE_STRING:
                            $cellValue = $cell->getValue();
                            if (is_array($cellValue) || is_object($cellValue)) {
                                $cellValue = $cell->getFormattedValue();
                            }
                            break;
                            break;
                        case DataType::TYPE_NUMERIC:
                            $cellValue = $cell->getFormattedValue();
                            break;
                        case DataType::TYPE_BOOL:
                            $cellValue = ($cell->getValue() ? 'TRUE' : 'FALSE');
                            break;
                        case DataType::TYPE_FORMULA:
                            $cellValue = $cell->getFormattedValue();
                            break;
                        default:
                            $cellValue = $cell->getValue();
                            if (is_array($cellValue) || is_object($cellValue)) {
                                $cellValue = $cell->getFormattedValue();
                            }
                            break;
                    }
                    $tmp[] = trim($cellValue);
                    if (empty($cellValue) && $col == 1) {
                        $isEmptyRow = true;
                    }
                }
                if ($tmp) {
                    $res[$row] = $tmp;
                }
                //如果本行为空，结束读取
                if ($isEmptyRow) {
                    break;
                }
            }
            return ($res);
        } catch (\PhpOffice\PhpSpreadsheet\Reader\Exception $e) {
            throw new \Exception($e->getMessage());
        }

    }
}
